package com.fanghaoran.notes.dao.impl;

import com.fanghaoran.notes.bean.CountByName;
import com.fanghaoran.notes.bean.CountByTid;
import com.fanghaoran.notes.bean.DiscountDetail;
import com.fanghaoran.notes.bean.DiscountUsageDetail;
import com.fanghaoran.notes.entity.*;
import com.fanghaoran.notes.dao.ProductDao;
import com.fanghaoran.notes.util.BaseDaoUtil;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @Author: win11
 * @Date: 2023/04/18/22:25
 * @Description:
 */
public class ProductDaoImpl implements ProductDao {
    @Override
    public CountByTid selectTotalCountByTid(int tId) throws Exception {
        String sql="select t_id,count(1) as count from product where t_id=?;";
        return BaseDaoUtil.executeQuery(CountByTid.class,sql,tId).get(0);
    }

    @Override
    public List<Product> selectProductByTidOrderBySales(int page, int pageSize, int tId) throws Exception {
        //分页: 偏移量 每页条数
        String sql="select * from product where t_id=? order by sales desc limit "+(page-1)*pageSize+","+ pageSize+";";
        return BaseDaoUtil.executeQuery(Product.class,sql,tId);
    }

    @Override
    public List<Product> selectProductByTidOrderByScore(int page, int pageSize, int tId) throws Exception {
        //分页: 偏移量 每页条数
        String sql="select * from product where t_id=? order by p_score desc limit "+(page-1)*pageSize+","+ pageSize+";";
        return BaseDaoUtil.executeQuery(Product.class,sql,tId);
    }

    @Override
    public CountByName selectTotalCountByName(String name) throws Exception {
        String sql="select ? as name,count(1) as count from product where p_name like ?;";
        return BaseDaoUtil.executeQuery(CountByName.class,sql,name,"%"+name+"%").get(0);
    }

    @Override
    public List<Product> selectProductByNameOrderBySales(int page, int pageSize, String name) throws Exception {
        //分页: 偏移量 每页条数
        String sql="select * from product where p_name like ? order by sales desc limit "+(page-1)*pageSize+","+ pageSize+";";
        return BaseDaoUtil.executeQuery(Product.class,sql,"%"+name+"%");
    }

    @Override
    public List<Product> selectProductByNameOrderByScore(int page, int pageSize, String name) throws Exception {
        //分页: 偏移量 每页条数
        String sql="select * from product where p_name like ? order by p_score desc limit "+(page-1)*pageSize+","+ pageSize+";";
        return BaseDaoUtil.executeQuery(Product.class,sql,"%"+name+"%");
    }

    @Override
    public List<Product> selectProductByPid(int pId) throws Exception {
        String sql="select * from product where p_id=?;";
        return BaseDaoUtil.executeQuery(Product.class,sql,pId);
    }

    @Override
    public Integer insertFavorite(int uId,int pId, int isFavorite) throws SQLException {
        String sql="insert into favorite (u_id,p_id,is_favorite) values(?,?,?);";
        return BaseDaoUtil.executeUpdate(sql,uId,pId,isFavorite);
    }

    @Override
    public List<Favorite> selectFavoriteByUidAndPid(Integer uId, int pId) throws Exception {
        String sql="select * from favorite where u_id=? and p_id=?;";
        return BaseDaoUtil.executeQuery(Favorite.class,sql,uId,pId);
    }

    @Override
    public Integer insertProduct(int uId,String productName, BigDecimal price, int stock, String productInfo, int type, String productFile) throws SQLException {
        String sql="insert into product (p_name,price,stock,p_info,t_id,p_image,u_id) values(?,?,?,?,?,?,?) ;";
        return BaseDaoUtil.executeUpdate(sql,productName,price,stock,productInfo,type,productFile,uId);
    }

    @Override
    public List<Product> selectProductByUid(Integer uId) throws Exception {
        String sql = "select * from product where u_id = ?;";
        return BaseDaoUtil.executeQuery(Product.class,sql,uId);
    }

    @Override
    public Integer updateProduct(int pId, String pName, BigDecimal price, int stock, String pInfo) throws SQLException {
        String sql = "update product set p_name=?,price=?,stock=?,p_info=? where p_id=?;";
        return BaseDaoUtil.executeUpdate(sql,pName,price,stock,pInfo,pId);
    }

    @Override
    public List<Product> selectProductRank() throws Exception {
        String sql= "select p_image,p_name,sales from product order by sales desc;";
        return BaseDaoUtil.executeQuery(Product.class,sql);
    }

    @Override
    public Integer updateProductAfterBuy(int pid,int sales, int stock) throws SQLException {
        String sql = "update product set sales=?,stock=? where p_id=?;";
        return BaseDaoUtil.executeUpdate(sql,sales,stock,pid);
    }

    @Override
    public Integer addDiscount(int pId, int type, int amount) throws SQLException {
        String sql = "insert into discount(p_id,t_id,d_amount) values(?,?,?);";
        return BaseDaoUtil.executeUpdate(sql,pId,type,amount);
    }

    @Override
    public List<DiscountDetail> selectDiscountByPid(int pId) throws Exception {
        String sql = "select d.p_id,t.t_id,t.d_name,t.d_count,d.d_amount from discount as d " +
                "join d_type as t on d.t_id=t.t_id " +
                "where d.p_id=?;";
        return BaseDaoUtil.executeQuery(DiscountDetail.class,sql,pId);
    }

    @Override
    public List<DiscountUsage> selectUsageOfDiscount(int pId, int uId) throws Exception {
        String sql = "select * from buyer_discount where p_id=? and u_id=?;";
        return BaseDaoUtil.executeQuery(DiscountUsage.class,sql,pId,uId);
    }

    @Override
    public Integer insertUsageOfDiscount(int pId, int uId, int tId, int dAmount, int dState) throws SQLException {
        String sql = "insert into buyer_discount(p_id,u_id,t_id,d_amount,d_state) values(?,?,?,?,?);";
        return BaseDaoUtil.executeUpdate(sql,pId,uId,tId,dAmount,dState);
    }

    @Override
    public List<Discount> selectDiscountByPidAndTid(int pId, int tId) throws Exception {
        String sql = "select * from discount where p_id=? and t_id=?;";
        return BaseDaoUtil.executeQuery(Discount.class,sql,pId,tId);
    }

    @Override
    public Integer updateDiscount(int pId, int tId, int after) throws SQLException {
        String sql = "update discount set d_amount=? where p_id=? and t_id=?;";
        return BaseDaoUtil.executeUpdate(sql,after,pId,tId);
    }

    @Override
    public List<DiscountUsageDetail> selectDiscountUsageDetail(int pId, int uId) throws Exception {
        String sql = "select b.p_id,b.u_id,b.t_id,t.d_name,t.d_count,b.d_amount,b.d_state from buyer_discount as b " +
                "join d_type as t on b.t_id=t.t_id " +
                "where b.p_id=? and b.u_id=?;" ;
        return BaseDaoUtil.executeQuery(DiscountUsageDetail.class,sql,pId,uId);
    }

    @Override
    public Integer updateUsageOfDiscount(int pId, int uId,int state) throws SQLException {
        String sql = "update buyer_discount set d_state=? where p_id=? and u_id=?;";
        return BaseDaoUtil.executeUpdate(sql,state,pId,uId);
    }

    @Override
    public Integer updateProductByPriceAndStock(int pId, BigDecimal price, int stock) throws SQLException {
        String sql = "update product set price=?,stock=? where p_id=?;";
        return BaseDaoUtil.executeUpdate(sql,price,stock,pId);
    }

    @Override
    public Integer updateProductScoreByPid(int pid) throws SQLException {
        String sql = "update product set p_score =(select format(AVG(e.score),2) as avg_score from evaluation as e " +
                "join item as i on e.i_id=i.i_id " +
                "where i.p_id=?) where p_id=?;";
        return BaseDaoUtil.executeUpdate(sql,pid,pid);
    }

    @Override
    public List<UserInvite> selectInviteDetailByPidAndUid(int pId, int uId) throws Exception {
        String sql = "select * from user_invite where p_id=? and u_id=?;";
        return BaseDaoUtil.executeQuery(UserInvite.class,sql,pId,uId);
    }

    @Override
    public List<UserInvite> selectInviteDetailByInviteCode(String inviteCode) throws Exception {
        String sql = "select * from user_invite where invite_code=?;";
        return BaseDaoUtil.executeQuery(UserInvite.class,sql,inviteCode);
    }

    @Override
    public Integer updateInviteCodeState(int codeState,String inviteCode) throws SQLException {
        String sql = "update user_invite set code_state=? where invite_code=?;";
        return BaseDaoUtil.executeUpdate(sql,codeState,inviteCode);
    }

    @Override
    public Integer insertInviteDetail(int pId, Integer uId, String inviteCode, BigDecimal refundCount, int codeState, LocalDateTime beginTime, LocalDateTime endTime) throws SQLException {
        String sql = "insert into user_invite (u_id,p_id,invite_code,refund_count,code_state,begin_time,end_time) " +
                "values(?,?,?,?,?,?,?);";
        return BaseDaoUtil.executeUpdate(sql,uId,pId,inviteCode,refundCount,codeState,beginTime,endTime);
    }

    @Override
    public Integer updateInviteCodeStateByPidAndUid(int pId, Integer uId, int codeState) throws SQLException {
        String sql = "update user_invite set code_state=? where p_id=? and u_id=?;";
        return BaseDaoUtil.executeUpdate(sql,codeState,pId,uId);
    }

    @Override
    public Integer updateInviteDetail(Integer uId, int codeState,String inviteCode) throws SQLException {
        String sql ="update user_invite set new_id =?,code_state=? where invite_code=? ;";
        return BaseDaoUtil.executeUpdate(sql,uId,codeState,inviteCode);
    }


}
